package com.ruoyi.tongji.run;

import com.ruoyi.tongji.run.manager.ManagerFactory;
import com.ruoyi.tongji.utils.DateUtil;
import com.ruoyi.tongji.utils.DateUtils;
import com.ruoyi.tongji.utils.JxlsUtils;
import com.ruoyi.tongji.utils.PlatformException;
import org.beetl.sql.core.*;
import org.beetl.sql.core.db.DBStyle;
import org.beetl.sql.core.db.MySqlStyle;
import org.beetl.sql.ext.DebugInterceptor;

import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.util.*;

/**
 * 职工任务数统计
 */
public class 全部浆员二次采浆统计 {

    public static Date dateRangeMonth = DateUtil.getDate("2024-11-19");
    public static String renyuanDateRangeStart = "202401010000000";
    public static String renyuanDateRangeEnd =   "202410122359999";



    public static String root = "E:\\文档\\2024\\每月统计\\" + DateUtils.getMonth(dateRangeMonth) + "月\\";

    public static SQLManager taibangSqlManager() {
        SQLManager sqlManager = ManagerFactory.taibangSqlManager();

        return sqlManager;
    }

    public static void main(String[] args) {
        SQLManager sqlManager = ManagerFactory.jiangzhanSqlManager();

        List<HashMap> staffList = new ArrayList<HashMap>();

        Map model = new HashMap();
        model.put("staffList", staffList);
        model.put("dateRange",renyuanDateRangeStart.substring(0,8) + "至" + renyuanDateRangeEnd.substring(0,8));
        HashMap staff = new HashMap();
        staff.put("name","全部");
        getStaff(staff);

        staffList.add(staff);

        System.out.println(model);









        String excelTemplate = "excelTemplates/员工/员工新浆员、超半年、检疫期（献浆后2个月内）.xls";
        try(InputStream is = Thread.currentThread().getContextClassLoader().getResourceAsStream(excelTemplate)) {
            if(is==null) {
                throw new PlatformException("模板资源不存在："+excelTemplate);
            }
            File f = new File(root + "\\程序查询\\全部新浆员、超半年、检疫期（献浆后2个月内）" +  renyuanDateRangeStart.substring(0,8) + "至" + renyuanDateRangeEnd.substring(0,8) + ".xls");
            f.getParentFile().mkdirs();
            FileOutputStream os = new FileOutputStream(f);
            JxlsUtils.exportExcel(is,os,model);
        } catch (IOException e) {
            throw new PlatformException(e.getMessage());
        }

    }


    public static Map getStaff(HashMap staff) {
        SQLManager sqlManager = taibangSqlManager();

        String days = "60";
        int count = 4;
        //时间范围内超半年人员
        List<HashMap> chaobannianList = sqlManager.execute(new SQLReady("select count(*) from (\n" +
                "\tselect donor.donorId as dId,donor.donorNo, donor.donorName,left(register.registerDate,8) as startDate,date_add(left(register.registerDate,8), interval " +days+" day) as endDate,\n" +
                "\t\t(select count(*) from t_bims_plasma_collection as c where c.donorId = dId and c.beginDate between startDate and endDate and c.weight > 0 and c.collectionNumberFlag = 'Y' ) as cCount\n" +
                "\tfrom t_bims_donor_register as register left join t_bims_donor as donor on register.donorId = donor.donorId where changeless = 'N' and donor.plasmaDate > register.recordDate and registerDate between ? and ? group by donor.donorNo\n" +
                ") as a order by a.cCount", new Object[] { renyuanDateRangeStart, renyuanDateRangeEnd}), HashMap.class);
        Long chaobannian = (Long)chaobannianList.get(0).get("count(*)");
        staff.put("chaobannianTotal",chaobannian);

        //时间范围内超半年人员，2个月内采浆次数大于1次的
        List<HashMap> chaobanniandayu1ciList = sqlManager.execute(new SQLReady("select count(*) from (\n" +
                "\tselect donor.donorId as dId,donor.donorNo, donor.donorName,left(register.registerDate,8) as startDate,date_add(left(register.registerDate,8), interval " +days+" day) as endDate,\n" +
                "\t\t(select count(*) from t_bims_plasma_collection as c where c.donorId = dId and c.beginDate between startDate and endDate and c.weight > 0 and c.collectionNumberFlag = 'Y' ) as cCount\n" +
                "\tfrom t_bims_donor_register as register left join t_bims_donor as donor on register.donorId = donor.donorId where changeless = 'N' and donor.plasmaDate > register.recordDate and registerDate between ? and ?  group by donor.donorNo\n" +
                ") as a where a.cCount >= " + count + " order by a.cCount", new Object[] { renyuanDateRangeStart, renyuanDateRangeEnd}), HashMap.class);
        Long chaobanniandayu1ci = (Long)chaobanniandayu1ciList.get(0).get("count(*)");
        staff.put("chaoCount",chaobanniandayu1ci.intValue());


        //时间范围内检疫期人员，2个月内采浆次数
        List<HashMap> jianyiqiList = sqlManager.execute(new SQLReady("select count(*) from (\n" +
                "select b.dId as ddId,b.startDate,b.endDate,\n" +
                " (select count(*) from t_bims_plasma_collection as c where c.donorId = dId and c.beginDate between startDate and endDate and c.weight > 0 and c.collectionNumberFlag = 'Y' ) as cCount\n" +
                " from (\n" +
                "select *, (TO_DAYS(DATE_FORMAT(left(rDate,8),'%Y%m%d'))-TO_DAYS(DATE_FORMAT(left(payDate,8),'%Y%m%d'))) as days from (\n" +
                "\tselect donor.donorNo,\n" +
                "\t\t\t\t\tdonor.plasmaDate as pDate, \n" +
                "\t\t\t\t\tdonor.donorId as dId,\n" +
                "\t\t\t\t\tregister.registerDate as rDate,\n" +
                "\t\t\t\t\tleft(register.registerDate,8) as startDate,date_add(left(register.registerDate,8), interval " +days+" day) as endDate,\n" +
                "\t\t\t\t\t(select pay.payDate from t_bims_expense_payment as pay where pay.donorId = dId and pay.paydate < rDate order by pay.payDate desc limit 0,1) as payDate\t\t\t\n" +
                "  from t_bims_donor_register as register left join t_bims_donor as donor on register.donorId = donor.donorId where  donor.plasmaDate > register.registerDate and changeless != 'N' and registerDate between ? and ? \n" +
                " ) as a) as b where b.days >= " +days+" order by b.days desc\n" +
                ") as aa order by aa.cCount", new Object[] { renyuanDateRangeStart, renyuanDateRangeEnd}), HashMap.class);
        Long jianyiqiCount = (Long)jianyiqiList.get(0).get("count(*)");
        staff.put("jianyiqiTotal",jianyiqiCount);

        //时间范围内检疫期人员，2个月内采浆次数
        List<HashMap> jianyiqidayu1ciList = sqlManager.execute(new SQLReady("select count(*) from (\n" +
                "select b.dId as ddId,b.startDate,b.endDate,\n" +
                " (select count(*) from t_bims_plasma_collection as c where c.donorId = dId and c.beginDate between startDate and endDate and c.weight > 0 and c.collectionNumberFlag = 'Y' ) as cCount\n" +
                " from (\n" +
                "select *, (TO_DAYS(DATE_FORMAT(left(rDate,8),'%Y%m%d'))-TO_DAYS(DATE_FORMAT(left(payDate,8),'%Y%m%d'))) as days from (\n" +
                "\tselect donor.donorNo,\n" +
                "\t\t\t\t\tdonor.plasmaDate as pDate, \n" +
                "\t\t\t\t\tdonor.donorId as dId,\n" +
                "\t\t\t\t\tregister.registerDate as rDate,\n" +
                "\t\t\t\t\tleft(register.registerDate,8) as startDate,date_add(left(register.registerDate,8), interval " +days+" day) as endDate,\n" +
                "\t\t\t\t\t(select pay.payDate from t_bims_expense_payment as pay where pay.donorId = dId and pay.paydate < rDate order by pay.payDate desc limit 0,1) as payDate\t\t\t\n" +
                "  from t_bims_donor_register as register left join t_bims_donor as donor on register.donorId = donor.donorId where  donor.plasmaDate > register.registerDate and changeless != 'N' and registerDate between ? and ? \n" +
                " ) as a) as b where b.days >= " +days+" order by b.days desc\n" +
                ") as aa where aa.cCount >= " + count + " order by aa.cCount", new Object[] { renyuanDateRangeStart, renyuanDateRangeEnd}), HashMap.class);
        Long jianyiqidayu1ciCount = (Long)jianyiqidayu1ciList.get(0).get("count(*)");
        staff.put("jianXianCount",jianyiqidayu1ciCount.intValue());


        //时间范围内新浆员，2个月内采浆次数
        List<HashMap> xinList = sqlManager.execute(new SQLReady("select count(*) from (" +
                "select donor.donorId as dId, CONCAT(left(donor.firstCollectDate,8),'0000000') as startDate,DATE_FORMAT(date_add(left(donor.firstCollectDate,8), interval "+days+" day),'%Y%m%d2300000') as endDate," +
                "(select count(*) from t_bims_plasma_collection as c where c.donorId = dId and c.beginDate between startDate and endDate and c.weight > 0 and c.collectionNumberFlag = 'Y' ) as cCount" +
                " from (" +
                "select * from t_bims_donor as donor where donor.firstCollectDate between ? and ?" +
                ") as donor" +
                ") as a order by a.cCount", new Object[] { renyuanDateRangeStart, renyuanDateRangeEnd}), HashMap.class);
        Long xinCount = (Long)xinList.get(0).get("count(*)");
        staff.put("xinTotal",xinCount.intValue());

        //时间范围内新浆员，2个月内采浆次数
        List<HashMap> xindayu1ciList = sqlManager.execute(new SQLReady("select count(*) from (" +
                "select donor.donorId as dId, CONCAT(left(donor.firstCollectDate,8),'0000000') as startDate,DATE_FORMAT(date_add(left(donor.firstCollectDate,8), interval "+days+" day),'%Y%m%d2300000') as endDate," +
                "(select count(*) from t_bims_plasma_collection as c where c.donorId = dId and c.beginDate between CONCAT(left(donor.firstCollectDate,8),'0000000') and DATE_FORMAT(date_add(left(donor.firstCollectDate,8), interval "+days+" day),'%Y%m%d2300000') and c.weight > 0 and c.collectionNumberFlag = 'Y' ) as cCount" +
                " from (" +
                "select * from t_bims_donor as donor where donor.firstCollectDate between ? and ?" +
                ") as donor" +
                ") as a where a.cCount >= " + count + " order by a.cCount", new Object[] { renyuanDateRangeStart, renyuanDateRangeEnd}), HashMap.class);
        Long xindayu1ciCount = (Long)xindayu1ciList.get(0).get("count(*)");
        staff.put("xinCount", xindayu1ciCount.intValue());

        return null;
    }
}
